package com.turing.common.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.turing.common.entity.ExportSheetInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.LinkedList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@Slf4j
//Excel导入导出工具类
public class ExcelUtils {

    /**
     * 导出缓存目录
     */
    public static final String EXPORT_PATH = "/tmp/export/";

    //	public static XSSFWorkbook createWorkBook(){
    //
    //	}
    private static NumberFormat numberFormat = NumberFormat.getNumberInstance();

    static {
        numberFormat.setGroupingUsed(false);
    }

    /**
     * 读取给定行列格子的值
     *
     * @param wb       读取excel的文档对象
     * @param column   列序号
     * @param rowIndex 行序号
     * @return 读取到的值
     */
    public static String getColumn(Workbook wb, int column, int rowIndex) {
        //Workbook wb = readExcel(cleanTextPath); //文件
        Sheet sheet = wb.getSheetAt(0); //sheet
        Row row = sheet.getRow(rowIndex);
        //        log.info(">>>>>>>>rowIndex:{}",rowIndex);
        if (row != null && row.getCell(column) != null) {
            String cellData = (String) getCellFormatValue(row.getCell(column));
            if (!StringUtils.isEmpty(cellData)) {
                return cellData.replaceAll(" ", "");
            }
        } else {
            //            if (row==null){
            //                log.warn("row is null");
            //            }else {
            //                log.warn("column of row is null.rowIndex is {}, column is {}",rowIndex,column);
            //            }
        }
        return "";
    }

    /**
     * 读取给定值，给定列上行的序号
     *
     * @param wb     读取excel的文档对象
     * @param column 列序号
     * @param text   文本
     * @return 读取到的序号
     */
    public static int getRowIndex(Workbook wb, int column, String text) {
        //Workbook wb = readExcel(cleanTextPath); //文件
        if (wb != null) {
            Sheet sheet = wb.getSheetAt(0); //sheet
            for (Row row : sheet) {
                Cell cell = row.getCell(column - 1);
                String cellData = (String) getCellFormatValue(cell);
                Log.info("###### >>>>>>>>> cell data is " + cellData + ", need text is " + text + ", " + text.equals(cellData));
                if (!StringUtils.isEmpty(cellData)) {
                    if (cellData.equals(text)) {
                        return cell.getRowIndex();
                    }
                }
            }
        }
        return -1;
    }

    /**
     * @param wb       读取excel的文档对象
     * @param rowIndex 行序号
     * @param text     文本
     * @return 读取到的列序号
     */
    public static int getColumnIndex(Workbook wb, int rowIndex, String text) {
        //Workbook wb = readExcel(cleanTextPath); //文件
        Sheet sheet = wb.getSheetAt(0); //sheet
        Row row = sheet.getRow(rowIndex - 1);
        for (Cell cell : row) {
            String cellData = (String) getCellFormatValue(cell);
            if (!StringUtils.isEmpty(cellData) && cellData.equals(text)) {
                return cell.getColumnIndex();
            }
        }
        return -1;
    }

    private static String getColumnSet(Workbook wb, int column, int startRow, int endRow) {
        //Workbook wb = readExcel(cleanTextPath); //文件
        Sheet sheet = wb.getSheetAt(0); //sheet
        Row row = null;
        String result = "";
        String cellData = null;
        for (int i = startRow - 1; i < endRow; i++) {
            System.out.println(i);
            row = sheet.getRow(i);
            if (row != null) {
                cellData = (String) getCellFormatValue(row.getCell(column - 1));
                result = cellData.replaceAll(" ", "");
                break;
            }
            //System.out.println(cellData);
        }
        return result;
    }

    /**
     * easypoi导出excel
     * @param fileName 文件名
     * @param fileType 文件类型--后缀
     * @param workbook workbook
     * @param response response对象
     * @author lcy
     * @date 2021/5/28 22:46
     **/
    public static void export(String fileName,String fileType,Workbook workbook,HttpServletResponse response) throws IOException{
        //设置返回值信息
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes)RequestContextHolder.getRequestAttributes();
        //设置文件名
        if (Tools.isNotEmpty(requestAttributes) && requestAttributes.getRequest().getHeader("user-agent").toLowerCase().contains("firefox")) {
            response.setHeader("Content-Disposition","attachment;filename=" + new String(fileName.getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1) + "." + fileType);
        } else {
            response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName,"utf-8") + "." + fileType);
        }
        // 导出
        try {
            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();
            response.getOutputStream().close();
            workbook.close();
        } catch (IOException e) {
            Log.error("export excel failed.",e);
        }
    }

    /**
     * @param wb       需要读取的文件excel对象
     * @param column   指定需要获取的列数，例如第一列 1
     * @param startRow 指定从第几行开始读取数据
     * @return 返回读取列数据的set
     */
    public static String getColumnSet(Workbook wb, int column, int startRow) {
        //Workbook wb = readExcel(cleanTextPath); //文件
        Sheet sheet = wb.getSheetAt(0); //sheet
        int rownum = sheet.getPhysicalNumberOfRows(); //行数
        System.out.println("sumrows " + rownum);

        return getColumnSet(wb, column, startRow, rownum - 1);
    }

    /**
     * @param filePath 需要读取的文件路径
     * @return 返回的excel对象
     */
    public static Workbook readExcel(String filePath) {
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(is);
            } else {
                return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * @param cell excel格子对象
     * @return 读到的值
     */
    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellTypeEnum()) {
                case NUMERIC: {
                    double d = cell.getNumericCellValue();
                    Object value = numberFormat.format(d);
                    cellValue = (value == null ? "" : value.toString());
                    break;
                }
                case FORMULA: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    public static void outputExcelByWorkbook(XSSFWorkbook workbook, OutputStream outputStream) {
        if (workbook != null && outputStream != null) {
            try {
                workbook.write(outputStream);
                outputStream.flush();

                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 对外提供读取excel 的方法
     */
    public static List<List<Object>> readExcel(File file) throws IOException {
        String extension = file.getName().substring(file.getName().lastIndexOf(".") + 1).toLowerCase();

        if ("xls".equals(extension)) {
            return read2003Excel(new FileInputStream(file));
        } else if ("xlsx".equals(extension)) {
            return read2007Excel(new FileInputStream(file));
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 读取 office 2003 excel
     */
    private static List<List<Object>> read2003Excel(InputStream stream) throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        HSSFWorkbook hwb = new HSSFWorkbook(stream);
        HSSFSheet sheet = hwb.getSheetAt(0);
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    linked.add("");
                    continue;
                }
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("###.####");// 格式化数字
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                            value = nf.format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    default:
                        value = cell.toString();
                }
                if (value == null || "".equals(value)) {
                    linked.add("");
                    continue;
                }
                linked.add(value);
            }
            list.add(linked);
        }
        return list;
    }

    /**
     * 读取Office 2007 excel
     */
    private static List<List<Object>> read2007Excel(InputStream stream) throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(stream);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        Object value = null;
        XSSFRow row = null;
        XSSFCell cell = null;

        for (int i = sheet.getFirstRowNum(); i <= sheet
                .getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    linked.add("");
                    continue;
                }
                value = cell.toString();

                if (value == null || "".equals(value)) {
                    linked.add("");
                    continue;
                }
                linked.add(value);
            }
            list.add(linked);
        }
        return list;
    }

    /**
     * easypoi导出excel
     *
     * @param exportSheetInfo sheet页信息
     * @param exportData      数据集合
     * @param outputStream    导出到指定流
     * @author lcy
     * @date 2021/5/28 22:45
     **/
    public static void export(ExportSheetInfo exportSheetInfo, List<Object> exportData, OutputStream outputStream) {

        // 创建参数对象（
        ExportParams exportParams = new ExportParams();
        //设置标题
        if (Tools.isNotEmpty(exportSheetInfo.getTitle())) {
            exportParams.setTitle(exportSheetInfo.getTitle());
        }
        // 设置sheet的名称
        exportParams.setSheetName(exportSheetInfo.getSheetName());
        //设置导出类型
        exportParams.setType(ExcelType.XSSF);
        // 导出
        try (Workbook workBook = ExcelExportUtil.exportExcel(exportParams, exportData.get(0).getClass(), exportData)) {
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            Log.error("export excel failed.", e);
        }
    }

    /**
     * easypoi导出excel
     *
     * @param fileName        文件名
     * @param exportSheetInfo sheet页信息
     * @param exportData      数据集合
     * @param response        response对象
     * @author lcy
     * @date 2021/5/28 22:46
     **/
    public static void export(String fileName, ExportSheetInfo exportSheetInfo, List<Object> exportData, HttpServletResponse response) throws IOException {
        //设置返回值信息
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        //设置文件名
        if (Tools.isNotEmpty(requestAttributes) && requestAttributes.getRequest().getHeader("user-agent").toLowerCase().contains("firefox")) {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1) + ".xlsx");
        } else {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8") + ".xlsx");
        }

        export(exportSheetInfo, exportData, response.getOutputStream());
    }

    /**
     * easypoi导出excel
     *
     * @param fileName 文件名
     * @param response response对象
     * @author lcy
     * @date 2021/5/28 22:46
     **/
    public static void export(String fileName, Workbook workbook, HttpServletResponse response) throws IOException {
        //设置返回值信息
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        //设置文件名
        if (Tools.isNotEmpty(requestAttributes) && requestAttributes.getRequest().getHeader("user-agent").toLowerCase().contains("firefox")) {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1) + ".xlsx");
        } else {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8") + ".xlsx");
        }
        // 导出
        try {
            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();
            response.getOutputStream().close();
            workbook.close();
        } catch (IOException e) {
            Log.error("export excel failed.", e);
        }
    }

    /**
     * easypoi导出excel的压缩包
     *
     * @param zipName   压缩包名称
     * @param workbooks 工作本
     * @param fileNames excel文件名列表
     * @param response  返回值对象
     * @author lcy
     * @date 2021/8/9 18:10
     **/
    public static void exportZip(String zipName, List<Workbook> workbooks, List<String> fileNames, HttpServletResponse response) throws IOException {
        String datetime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS"));
        zipName = zipName + "_" + datetime;
        //设置返回值信息
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream;charset=UTF-8");
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        //设置文件名
        if (Tools.isNotEmpty(requestAttributes) && requestAttributes.getRequest().getHeader("user-agent").toLowerCase().contains("firefox")) {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(zipName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1) + ".zip");
        } else {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(zipName, "utf-8") + ".zip");
        }

        File root = new File(EXPORT_PATH + datetime);
        //文件不存在--创建
        if (!root.exists()) {
            root.mkdirs();
        }

        // 导出
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
             ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream)) {
            for (int i = 0; i < workbooks.size(); i++) {
                Workbook workbook = workbooks.get(i);
                File file = new File(EXPORT_PATH + datetime + "/" + fileNames.get(i) + "_" + datetime + ".xlsx");
                //文件不存在创建
                if (!file.exists()) {
                    file.createNewFile();
                }
                try (FileOutputStream fileOutputStream = new FileOutputStream(file)) {
                    workbook.write(fileOutputStream);
                    pullZipFile(zipOutputStream, file);
                }
            }
            IOUtils.closeQuietly(zipOutputStream);
            root.deleteOnExit();
            response.addHeader("Content-Length", "" + outputStream.toByteArray().length);
            IOUtils.write(outputStream.toByteArray(), response.getOutputStream());
        } catch (IOException e) {
            Log.error("export zip failed.", e);
        }
    }

    /**
     * 添加压缩文件
     *
     * @param zipOutputStream 压缩文件流
     * @param file            需要压缩的文件
     * @author lcy
     * @date 2021/8/9 14:51
     **/
    public static void pullZipFile(ZipOutputStream zipOutputStream, File file) throws IOException {
        ZipEntry entry = new ZipEntry("/" + file.getName());
        zipOutputStream.putNextEntry(entry);
        zipOutputStream.write(org.apache.commons.io.FileUtils.readFileToByteArray(file));
        zipOutputStream.flush();
        zipOutputStream.closeEntry();
    }

    /***
     * 通过反射重新
      * @param null
     * @return {@link {@link null}}
     * @throws
     * @author zhiqiang
     * @date 2022/8/30 15:06
     */
    public static void resetCellMaxTextLength() {
        SpreadsheetVersion excel2007 = SpreadsheetVersion.EXCEL2007;
        if (Integer.MAX_VALUE != excel2007.getMaxTextLength()) {
            Field field;
            try {
                field = excel2007.getClass().getDeclaredField("_maxTextLength");
                field.setAccessible(true);
                field.set(excel2007,Integer.MAX_VALUE);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}
